package com.xgpro.mapper.studycentre;

import com.xgpro.domain.studycentre.entity.Course;
import com.xgpro.domain.studycentre.vo.*;
import org.apache.ibatis.annotations.*;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;

/**
 * @author tx
 * @version 1.0
 * @date 2019/8/25 12:10
 */
public interface CourseMapper extends Mapper<Course> {

    /**
     * 根据课程状态查询课程信息
     * @param status
     * @return
     */
    @Select("<script> " +
            "select t_course.id as id,t_course.dept_id as deptId,t_course.title as title,t_course.open_Time as openTime ,t_course.status as status ,t_user.photo as photo ,t_user.id as userId,t_user.username as username ,t_deptment.name as deptName " +
            "from t_course left join t_user on t_course.tea_id=t_user.id left join t_deptment on t_deptment.id=t_user.dept_id " +
            "<where>" +
            "  t_course.status=#{status}  and t_course.course_type=0 and t_course.dept_id  in" +
            " <foreach collection='list' index='index' item='id'  open='(' separator=',' close=')'> " +
            "  #{id}" +
            " </foreach>" +
            " </where>" +
            "order by t_course.update_time DESC" +
            "</script>")
    List<CourseBack> selectByStatus(@Param("status") Integer status,@Param("list") List<Integer> list);


    /**
     * 根据课程状态查询课程信息
     * @param status
     * @return
     */
    @Select("<script> " +
            "select t_course.id as id,t_course.dept_id as deptId,t_course.title as title,t_course.open_Time as openTime ,t_course.status as status ,t_user.photo as photo ,t_user.id as userId,t_user.username as username ,t_deptment.name as deptName " +
            "from t_course left join t_user on t_course.tea_id=t_user.id left join t_deptment on t_deptment.id=t_user.dept_id " +
            "where t_course.status=#{status} and  t_course.course_type=0 order by t_course.update_time DESC" +
            "</script>")
    List<CourseBack> selectByStatusForCom(@Param("status") Integer status);


    /**
     * 根据课程状态查询待审批课程信息
     * @return
     */
    @Select("<script> " +
            "select t_course.id as id,t_course.dept_id as deptId,t_course.title as title,t_course.open_Time as openTime ,t_course.status as status ,t_user.photo as photo ,t_user.id as userId,t_user.username as username ,t_deptment.name as deptName " +
            "from t_course left join t_user on t_course.tea_id=t_user.id left join t_deptment on t_deptment.id=t_user.dept_id " +
            "where t_course.status=0 or  t_course.status=3 and t_course.course_type=0  order by t_course.update_time DESC" +
            "</script>")
    List<CourseBack> selectByStatusForCompany();
    /**
     * 查询课程信息
     * @param id
     * @return
     */
    @Select("<script> " +
            "select t_course.id as id ,t_course.photo as coursePhoto,t_course.title as title ,t_course.small_title as smallTitle,t_course.address as address,t_course.open_time as openTime,t_course.course_time as courseTime,t_course.introduce as introduce,t_user.id as userId,t_user.photo as photo,t_user.username as username,t_course.qr as qr,t_course.flag as flag,t_course.course_dept as courseDept,t_course.tea_status as teaStatus " +
            "from t_course left join t_user on t_course.tea_id=t_user.id " +
            "where t_course.id=#{id}" +
            "</script>")
    CourseInfo selectCourseInfoById(Integer id);

    /**
     * 我的课程待讲列表
     * @param id
     * @return
     */
    @Select("select t_course.id as id,t_course.title as title,t_course.small_title as smallTitle,t_course.open_Time as openTime ,t_course.status as status ,t_user.photo as photo ,t_user.id as userId,t_user.username as username ,t_deptment.name as deptName ,t_course.is_open as isOpen" +
            " from t_course left join t_user on t_course.tea_id=t_user.id left join t_deptment on t_deptment.id=t_user.dept_id " +
            " where t_course.tea_id=#{id} and t_course.tea_status=0 and t_course.status=1 order by t_course.update_time DESC")
    List<CourseBack> selectOpenList(Integer id);

    /**
     * 我的课程审批列表
     * @param id
     * @return
     */
    @Select("select t_course.id as id,t_course.title as title,t_course.open_Time as openTime ,t_course.status as status ,t_user.photo as photo ,t_user.id as userId,t_user.username as username ,t_deptment.name as deptName ,t_course.is_open as isOpen" +
            " from t_course left join t_user on t_course.tea_id=t_user.id left join t_deptment on t_deptment.id=t_user.dept_id " +
            " where t_course.tea_id=#{id} and (t_course.status=0 OR t_course.status=3) order by t_course.update_time DESC")
    List<CourseBack> selectAppList(Integer id);

    /**
     * 我的课程完成列表
     * @param id
     * @return
     */
    @Select("select t_course.id as id,t_course.title as title,t_course.open_Time as openTime ,t_course.status as status ,t_user.photo as photo ,t_user.id as userId,t_user.username as username ,t_deptment.name as deptName ,t_course.tea_status as teaStatus " +
            " from t_course left join t_user on t_course.tea_id=t_user.id left join t_deptment on t_deptment.id=t_user.dept_id " +
            " where t_course.tea_id=#{id} and t_course.status=1 and t_course.tea_status!=0 order by t_course.update_time DESC")
    List<CourseBack> selectAchieveList(Integer id);

    /**
     * 查询对应部门负责人的id
     * @param deptId
     * @param deptRoleId
     * @return
     */
    @Select("select t_user.id from t_user where t_user.second_dept_id=#{deptId} and t_user.id in (select user_id  from t_user_role where role_id=#{deptRoleId} )")
    List<Integer> selectDeptUserIds(@Param("deptId") Integer deptId,@Param("deptRoleId") Integer deptRoleId);

    /**
     * 查询对应公司负责人的id
     * @param companyRoleId
     * @return
     */
    @Select("select t_user.id from t_user where t_user.id in (select user_id  from t_user_role where role_id=#{companyRoleId} )")
    List<Integer> selectCompanyUserIds(Integer companyRoleId);

    /**
     * 添加指定人入课程
     * @param courseId
     * @param studentsList
     * @return
     */
    @Insert("<script>" +
            " insert into t_user_course(student_id,course_id) values " +
            "<foreach collection=\"list\" item=\"id\" separator=\",\">" +
            "  (#{id},#{courseId})" +
            "</foreach>" +
            "</script>")
    Integer insertUserCourse(@Param("courseId") Integer courseId,@Param("list") List<Integer> studentsList);

    /**
     * 查询报名列表
     * @param courseIds
     * @return
     */
    @Select("<script>" +
            "select t_course.id as id,t_course.update_time as updateTime,t_course.title as title,t_course.course_type as courseType,t_course.open_Time as openTime ,t_course.photo as photo ,t_user.id as userId,t_user.username as username ,t_deptment.name as deptName,t_course.annx_id as annxId " +
            " from t_course left join t_user on t_course.tea_id=t_user.id left join t_deptment on t_deptment.id=t_user.dept_id "+
            " <where>" +
            " t_course.id in" +
            " <foreach collection='list' index='index' item='id'  open='(' separator=',' close=')'> " +
            "  #{id}" +
            " </foreach>" +
            " </where>" +
            " order by t_course.update_time DESC" +
            "</script>")
    List<CourseSignBack> selectCourseSignBackList(List<Integer> courseIds);

    /**
     * 查询参加和未参加的课程
     * @param courseIds
     * @return
     */
    @Select("<script>" +
            "select t_course.id as id,t_course.title as title,t_course.open_Time as openTime,t_user.photo as photo ,t_user.id as userId,t_user.username as username ,t_deptment.name as deptName,t_course.course_type as courseType,t_course.photo as coursePhoto,t_course.annx_id as annxId " +
            " from t_course left join t_user on t_course.tea_id=t_user.id left join t_deptment on t_deptment.id=t_user.dept_id " +
            "<where>" +
            " t_course.id in " +
            " <foreach collection='list' index='index' item='id'  open='(' separator=',' close=')'> " +
            "  #{id}" +
            " </foreach>" +
            "</where>" +
            " order by t_course.update_time DESC" +
            "</script> "
    )
    List<MySignCourseBack> qrySignCourse(List<Integer> courseIds);

    /**
     * 根据课程种类查询
     * @param courseIds
     * @param courseType
     * @return
     */
    @Select("<script>" +
            "select t_course.id as id,t_course.title as title,t_course.open_Time as openTime,t_user.photo as photo ,t_user.id as userId,t_user.username as username ,t_deptment.name as deptName,t_course.course_type as courseType,t_course.photo as coursePhoto,t_course.annx_id as annxId " +
            " from t_course left join t_user on t_course.tea_id=t_user.id left join t_deptment on t_deptment.id=t_user.dept_id " +
            "<where>" +
            " t_course.course_type=#{courseType} and" +
            " t_course.id in " +
            " <foreach collection='list' index='index' item='id'  open='(' separator=',' close=')'> " +
            "  #{id}" +
            " </foreach>" +
            "</where> " +
            " order by t_course.update_time DESC" +
            "</script> "
    )
    List<MySignCourseBack> qrySignCourseByCourseType(@Param("list") List<Integer> courseIds,@Param("courseType") Integer courseType);

    /**
     * 根据userid查询课程
     * @param id
     * @return
     */
    @Select("select t_course.id as id,t_course.title as title,t_course.open_time as openTime from t_course where tea_id =#{id} and status=1 and flag=1 and is_open=1 order by open_time DESC")
    List<CourseSimple> selectCourseSimple(Integer id);

    /**
     *
     * 根据userid查询精品课程
     * @param id
     * @return
     */
    @Select("select t_course.id as id,t_course.title as title,t_course.photo as photo from t_course where tea_id =#{id} and status=1 and flag=1 and is_open=1 and is_good=1 order by open_time DESC")
    List<GoodCourse> selectGoodCourse(Integer id);


    /**
     * 查询开课程课
     * @param courseOpenPCFront
     * @return
     */
    @Select("<script>" +
            " select t_course.id as id, t_course.title as title,t_user.username as username,t_course.course_type as courseType, t_course.introduce as introduce from " +
            " t_course left join t_user on t_course.tea_id = t_user.id  " +
            "<where>" +
            " status=1" +
            " <if test=\"courseOpenPCFront.title != null and courseOpenPCFront.title!='' \"> " +
            "    and  t_course.title like CONCAT('%',#{courseOpenPCFront.title},'%')" +
            " </if>" +
            " <if test=\"courseOpenPCFront.username != null and courseOpenPCFront.username!='' \"> " +
            "  and t_user.username like CONCAT('%',#{courseOpenPCFront.username},'%')  " +
            " </if>" +
            " <if test=\"courseOpenPCFront.courseType != null and courseOpenPCFront.courseType!='' or courseOpenPCFront.courseType==0 \"> " +
            "  and t_course.course_type=#{courseOpenPCFront.courseType} " +
            " </if>" +
            "</where> " +
            " order by t_course.update_time DESC" +
            "</script>")
    List<CourseOpenPCBack> selectCourseOpenResult(@Param("courseOpenPCFront") CourseOpenPCFront courseOpenPCFront);

    /**
     * 批量删除课程
     * @param ids
     * @return
     */
    @Delete("<script>" +
            " delete from t_course where  id in " +
            " <foreach collection='list' index='index' item='id'  open='(' separator=',' close=')'> " +
            "  #{id}" +
            " </foreach>" +
            " </script>")
    Integer deleteCourseOpenByIds(List<Integer> ids);

    /**
     * 查询微课程
     * @param id
     * @return
     */
    @Select("select t_course.id as id,t_course.title as name,t_course.introduce as introduce,t_attachment.file_type as fileType,t_attachment.id as annxId,t_attachment.name as fileName,t_attachment.file_path as filePath,t_course.photo as photo,t_course.small_type as smallType " +
            " from t_course left join t_attachment on t_course.annx_id=t_attachment.id " +
            " where t_course.id=#{id} order by t_course.update_time DESC")
    SmallCourseBack qrySmallCourseById(Integer id);
}
